package com.show.dao.population;

import com.show.dto.population.Population;
import com.show.dto.sensor.GasSensorError;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.List;

/**
 * 基站附近人口
 *
 * @author 姜文
 */
public interface PopulationRepository extends JpaRepository<Population, String>, JpaSpecificationExecutor<Population>, PagingAndSortingRepository<Population, String> {

    /**
     * 大屏障人口展示
     *
     * @author 姜文
     */
    @Query(value = "SELECT\n" +
            "\tCOUNT(*) AS countPopulation,\n" +
            "\tbs.base_station_name AS baseStationName\n" +
            "FROM\n" +
            "\t`population` AS p\n" +
            "LEFT JOIN base_station AS bs ON p.base_station_id = bs.base_station_id\n" +
            "WHERE\n" +
            "\tp.cdate BETWEEN date_sub(NOW(), INTERVAL 1 HOUR)\n" +
            "AND NOW()\n" +
            "GROUP BY\n" +
            "\tp.base_station_id",nativeQuery = true)
    List<Population> findPopulation();

}
